package com.projectY.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Random;

import com.projectY.settings.Settings;

public class DataDb extends Settings{
	 Statement stmt = null;
 
	public void callDb() throws SQLException
	{
		stmt = Settings.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		String sql;
		sql = "SELECT * FROM testtable";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			System.out.println(rs.getInt(1));
		}
	}
	public void insertDummyData(ArrayList<String> links,String user,ArrayList<String> friends) throws SQLException
	{
		stmt = Settings.conn.createStatement();
		Random randomGenerator = new Random();
		for(int i=0;i<links.size();i++)
	      {String sql = "INSERT INTO linksTable " +
	                   "VALUES ('"+links.get(i)+"', '"+friends.get(randomGenerator.nextInt(5))+"')";
	      System.out.println(sql);
	      stmt.executeUpdate(sql);
	      }
	}
	
	public int Vote(String link,String user,int voteType,String query, int tty) 
	{
		try{
		if(ifVoted(link,user))
		{
			String insertTableSQL = "update   linksTable set vote=1, modified_date='"+new Date(new java.util.Date().getTime())+"',query=?,tty=? where links=? and userid=?";
			PreparedStatement preparedStatement = Settings.conn.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, query);
			preparedStatement.setString(2, link);
			preparedStatement.setString(3, user);
			preparedStatement.setInt(4, tty);
			preparedStatement .executeUpdate();
			return 1;
		}
		System.out.println("Link "+link+" User "+user+" Vote "+ voteType+" Query "+query);
		String insertTableSQL = "INSERT INTO linksTable (links,userid,modified_date,vote,query,tty) VALUES"
				+ "(?,?,?,?,?,?)";
		PreparedStatement preparedStatement = Settings.conn.prepareStatement(insertTableSQL);
		preparedStatement.setString(1, link);
		preparedStatement.setString(2, user);
		preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));
		preparedStatement.setInt(4, voteType);
		preparedStatement.setString(5, query);
		preparedStatement.setInt(6, tty);
		preparedStatement .executeUpdate();
		return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	}
	public int downVote(String link,String user,int voteType,String query) 
	{
		try{
		if(ifVoted(link,user))
		{
			
			String insertTableSQL = "update   linksTable set vote=0, modified_date='"+new Date(new java.util.Date().getTime())+"',query=? where links=? and userid=?";
			PreparedStatement preparedStatement = Settings.conn.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, query);
			preparedStatement.setString(2, link);
			preparedStatement.setString(3, user);
			preparedStatement .executeUpdate();
			return 1;
		}
		String insertTableSQL = "INSERT INTO linksTable (links,userid,modified_date,vote,query) VALUES"
				+ "(?,?,?,?,?)";
		PreparedStatement preparedStatement = Settings.conn.prepareStatement(insertTableSQL);
		preparedStatement.setString(1, link);
		preparedStatement.setString(2, user);
		preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));
		preparedStatement.setInt(4, voteType);
		preparedStatement.setString(5, query);
		preparedStatement .executeUpdate();
		return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	}
	public boolean ifVoted(String link, String user) throws SQLException {

		String selectSQL = "SELECT *FROM linksTable WHERE userid = ? and links=?";
		PreparedStatement preparedStatement = Settings.conn.prepareStatement(selectSQL);
		preparedStatement.setString(1, user);
		preparedStatement.setString(2, link);
		
		ResultSet rs = preparedStatement.executeQuery(  );
		if(rs.next())
		{


				return true;
			
			
		}
		return false;
	}
	public boolean checkIfUpVoted(String link, String user) throws SQLException {

		String selectSQL = "SELECT *FROM linksTable WHERE userid = ? and links=?";
		PreparedStatement preparedStatement = Settings.conn.prepareStatement(selectSQL);
		preparedStatement.setString(1, user);
		preparedStatement.setString(2, link);
		
		ResultSet rs = preparedStatement.executeQuery(  );
		if(rs.next())
		{
			if(rs.getInt(4)==1)
			return true;
			Date mDate=rs.getDate(3);
			System.out.println(mDate.getTime());
			System.out.println(new java.util.Date().getTime());
			System.out.println(mDate.getTime()-new java.util.Date().getTime());
			System.out.println((mDate.getTime()-new java.util.Date().getTime())/1000/60/60/24);
			int mon=(int) ((mDate.getTime()-new java.util.Date().getTime())/1000/60/60/24);
			mon =-mon;
			if(mon<30&&rs.getInt(4)==0)
				return true;
			
			
		}
		return false;
	}
	public HashMap<String,Integer>   readLinksProfile(ArrayList<String> links ,ArrayList<String> friends) throws SQLException
	{
		String sql="select * from linksTable where links in (";
		for(int i=0;i<links.size();i++)
		{
			if(i==0)
				{
				sql =sql +"'"+links.get(i)+"'";
				continue;
				}
			sql = sql +","+"'"+links.get(i)+"'";
			
		}
		sql= sql + ") and userid in (";
		for(int i=0;i<friends.size();i++)
		{
			if(i==0)
				{
				sql =sql +"'"+friends.get(i)+"'";
				continue;
				}
			sql = sql +","+"'"+friends.get(i)+"'";
			
		}
		sql= sql + ")  ";
		stmt = Settings.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = stmt.executeQuery(sql);
		System.out.println("SQL is "+ sql);
		HashMap<String,Integer> res= new HashMap<String,Integer>();
		while(rs.next())
		{
//			System.out.println(rs.getString(1)+" "+ rs.getString(2));
			if(res.containsKey(rs.getString(1)))
			{
				int value=res.get(rs.getString(1))+1;
				res.put(rs.getString(1), value);
			}
			else
				res.put(rs.getString(1),1);
		}
		return res;
		
	}
	public static void main(String q[])  {
//		ArrayList<String> links =new ArrayList<String>();
//		links.add("http://www.cs.utexas.edu/users/djimenez/utsa/cs1723/lecture2.html");
//		links.add("http://en.wikipedia.org/wiki/Heap_(data_structure)");
//		links.add("http://www.studytonight.com/data-structures/time-complexity-of-algorithms");
//		links.add("http://www.cs.princeton.edu/courses/archive/spring06/cos423/Handouts/Amortized.pdf");
//		links.add("http://www.leda-tutorial.org/en/official/ch02s02s03.html");
//		links.add("http://dl.acm.org/citation.cfm?id=73040");
//		
//		ArrayList<String> friends = new ArrayList<String>();
//		friends.add("ishu");
//		friends.add("akshay");
//		friends.add("himank");
//		friends.add("vrinda");
//		friends.add("arushi");
//		
//		
//		new DataDb().readLinksProfile(links, friends);
//		new DataDb().readLinksProfile(links, friends);

	}
	public  List<String> upVotingFriends(String link, ArrayList<String> friends) throws SQLException {

		String sql="select * from linksTable where links in (";
		sql =sql +"'"+link+"'";
		sql= sql + ") and userid in (";
		for(int i=0;i<friends.size();i++)
		{
			if(i==0)
				{
				sql =sql +"'"+friends.get(i)+"'";
				continue;
				}
			sql = sql +","+"'"+friends.get(i)+"'";
			
		}
		sql= sql + ")  ";
		stmt = Settings.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = stmt.executeQuery(sql);
		System.out.println("SQL is "+ sql);
		List<String> res= new ArrayList<String>();
		while(rs.next())
		{
			res.add(rs.getString(2));
		}
		return res;
	}
	public String isVoted(String string, String string2) {
		
		return null;
	}
	public int storeTimTaken(String link, String twitter, String fb, String gapi, int total) {
		

		try{
		if(ifVoted(link,fb))
		{
			String insertTableSQL = "update   linksTable set  modified_date='"+new Date(new java.util.Date().getTime())+"',  totalTime=? where links=? and (userid=? or userid=? or userid=?  )";
			PreparedStatement preparedStatement = Settings.conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, total);
			preparedStatement.setString(2, link);
			preparedStatement.setString(3, fb);
			preparedStatement.setString(4, twitter);
			preparedStatement.setString(5, gapi);
			
			preparedStatement .executeUpdate();
			return 1;
		}
		System.out.println("Link "+link+" User "+fb+" Vote " );
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	
		return 0;
	}
}
